import json
from datetime import datetime

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
from config import DB_URL

engine = create_engine(DB_URL)

users_table_structure = {
    'table_name': 'users',
    'columns': [
        {
            'name': 'id',
            'type': 'INT',
            'attributes': ['AUTO_INCREMENT', 'PRIMARY KEY']
        },
        {
            'name': 'username',
            'type': 'VARCHAR(50)',
            'attributes': ['NOT NULL']
        },
        {
            'name': 'email',
            'type': 'VARCHAR(100)',
            'attributes': ['NOT NULL', 'UNIQUE']
        },
        {
            'name': 'password',
            'type': 'VARCHAR(255)',
            'attributes': ['NOT NULL']
        },
        {
            'name': 'created_at',
            'type': 'TIMESTAMP',
            'attributes': ['DEFAULT CURRENT_TIMESTAMP']
        }
    ]
}
# 新增用户
def create_user(username, email, password):
    create_sql = text('INSERT INTO users (username, email, password) VALUES (:username, :email, :password)')
    try:
        with engine.connect() as conn:
            conn.execute(create_sql, {'username': username, 'email': email, 'password': password})
            conn.commit()
            print('User created successfully')
    except SQLAlchemyError as e:
        print(f'An error occurred while creating the user: {e}')
        # conn.rollback()


# 删除指定用户
def delete_user(username):
    delete_sql = text('DELETE FROM users WHERE username = :username')
    try:
        with engine.connect() as conn:
            conn.execute(delete_sql, {'username': username})
            conn.commit()
            print('User deleted successfully')
    except SQLAlchemyError as e:
        print(f'An error occurred while deleting user: {e}')


# 修改用户
def update_user(old_username, new_username, email, password):
    update_sql = text(
        'UPDATE users SET username = :new_username, email = :email, password = :password WHERE username = :old_username')

    try:
        with engine.connect() as conn:
            # 传递参数字典给 execute 方法
            conn.execute(update_sql, {
                'old_username': old_username,
                'new_username': new_username,
                'email': email,
                'password': password
            })
            conn.commit()
            print("User updated successfully")
    except SQLAlchemyError as e:
        print(f'An error occurred while updating the user: {e}')


# 查询所有用户
def select_user():
    select_sql = text('SELECT * FROM users')
    with engine.connect() as conn:
        try:
            result=conn.execute(select_sql)
            conn.commit()
            rows=result.fetchall()
            column_names = [column['name'] for column in users_table_structure['columns']]
            news_list = [dict(zip(column_names, row)) for row in rows]
            for news_item in news_list:
                for key, value in news_item.items():
                    if isinstance(value, datetime):
                        news_item[key] = value.isoformat()
            news_json = json.dumps(news_list, indent=4)
            print(news_json)
            return news_json
            print('User selected successfully')
        except SQLAlchemyError as e:
            print(f'An error occurred while selecting user: {e}')


# 查询指定用户
def select_by_username(username):
    select_sql = text("SELECT * FROM users WHERE username = :username")
    try:
        with engine.connect() as conn:
            sql = conn.execute(select_sql, {'username': username})
            conn.commit()
            print('User selected successfully')
            print(sql)
    except SQLAlchemyError as e:
        print(f'An error occurred while selecting user: {e}')


# test of 创建
# create_user(username='admin', email='dddssdads', password='admin')
# create_user(username='ad', email='sheshou', password='sheshou')
# create_user(username='fu', email='fuzhu', password='fuzhu')

# test of 删除
# delete_user('admin')

#test of 修改
# update_user(old_username='ad', new_username='new_ad', email='123456@example.com', password='hashed_password')

# test of 查询
# select_user()

# test of 查询指定用户
# select_byusername(username='admin')

# with engine.connect() as conn:
#     # 执行原生SQL语句
#     sql = text("select * from users")
#     results = conn.execute(sql)
#     # 遍历所有数据
#     for result in results:
#         print(result)
select_user()